- frmHostelFeePaymentRecord.vb
- project /
1 Imports System.Data.SqlClient
2 Imports System.IO
3 Imports Excel = Microsoft.Office.Interop.Excel
4 Public Class frmHostelFeePaymentRecord
5 Public Sub GetData()
6 Try
7 con = New SqlConnection(cs)
8 con.Open()
9 cmd = New SqlCommand("Select RTRIM(HostelFeePayment.Id) as [ID], RTRIM(HFP_ID) as [HFP ID], RTRIM(PaymentID) as [Payment ID],RTRIM(HostelerID) as [Hosteler ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(Hostelname) as [Hostel name],RTRIM(HostelFeePayment.Class) as [Class],RTRIM(HostelFeePayment.Section) as [Section], RTRIM(HostelFeePayment.Session) as [Session],RTRIM(installment) as [installment], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(HostelFeePayment.ClassType) as [Class Type], RTRIM(HostelFeePayment.SchoolType) as [School Type] from Student,SchoolInfo,HostelFeePayment,Hosteler,Hostelinfo where SchoolInfo.S_ID=Student.SchoolID and Hosteler.H_ID=HostelFeePayment.HostelerID and HostelInfo.HI_ID=Hosteler.HostelID and Student.AdmissionNo=Hosteler.AdmissionNo order by StudentName", con)
10 adp = New SqlDataAdapter(cmd)
11 ds = New DataSet()
12 adp.Fill(ds, "Student")
13 dgw.DataSource = ds.Tables("Student").DefaultView
14 con.Close()
15 Catch ex As Exception
16 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
17 End Try
18 End Sub
19
20 Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
21 Me.Close()
22 End Sub
23
24 Private Sub txtStudentName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStudentName.TextChanged
25 Try
26 con = New SqlConnection(cs)
27 con.Open()
28 cmd = New SqlCommand("Select RTRIM(HostelFeePayment.Id) as [ID], RTRIM(HFP_ID) as [HFP ID], RTRIM(PaymentID) as [Payment ID],RTRIM(HostelerID) as [Hosteler ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(Hostelname) as [Hostel name],RTRIM(HostelFeePayment.Class) as [Class],RTRIM(HostelFeePayment.Section) as [Section], RTRIM(HostelFeePayment.Session) as [Session],RTRIM(installment) as [installment], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(HostelFeePayment.ClassType) as [Class Type], RTRIM(HostelFeePayment.SchoolType) as [School Type] from Student,SchoolInfo,HostelFeePayment,Hosteler,Hostelinfo where SchoolInfo.S_ID=Student.SchoolID and Hosteler.H_ID=HostelFeePayment.HostelerID and HostelInfo.HI_ID=Hosteler.HostelID and Student.AdmissionNo=Hosteler.AdmissionNo and StudentName like '" & txtStudentName.Text & "%' order by StudentName", con)
29 adp = New SqlDataAdapter(cmd)
30 ds = New DataSet()
31 adp.Fill(ds, "Student")
32 dgw.DataSource = ds.Tables("Student").DefaultView
33 con.Close()
34 Catch ex As Exception
35 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
36 End Try
37 End Sub
38
39 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
40 Try
41 con = New SqlConnection(cs)
42 con.Open()
43 cmd = New SqlCommand("Select RTRIM(HostelFeePayment.Id) as [ID], RTRIM(HFP_ID) as [HFP ID], RTRIM(PaymentID) as [Payment ID],RTRIM(HostelerID) as [Hosteler ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(Hostelname) as [Hostel name],RTRIM(HostelFeePayment.Class) as [Class],RTRIM(HostelFeePayment.Section) as [Section], RTRIM(HostelFeePayment.Session) as [Session],RTRIM(installment) as [installment], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(HostelFeePayment.ClassType) as [Class Type], RTRIM(HostelFeePayment.SchoolType) as [School Type] from Student,SchoolInfo,HostelFeePayment,Hosteler,Hostelinfo where SchoolInfo.S_ID=Student.SchoolID and Hosteler.H_ID=HostelFeePayment.HostelerID and HostelInfo.HI_ID=Hosteler.HostelID and Student.AdmissionNo=Hosteler.AdmissionNo and HostelFeePayment.Session=@d1 and HostelFeePayment.Class=@d2 order by StudentName", con)
44 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
45 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
46 adp = New SqlDataAdapter(cmd)
47 ds = New DataSet()
48 adp.Fill(ds, "Student")
49 dgw.DataSource = ds.Tables("Student").DefaultView
50 con.Close()
51 Catch ex As Exception
52 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
53 End Try
54 End Sub
55
56 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
57 Try
58 con = New SqlConnection(cs)
59 con.Open()
60 cmd = New SqlCommand("Select RTRIM(HostelFeePayment.Id) as [ID], RTRIM(HFP_ID) as [HFP ID], RTRIM(PaymentID) as [Payment ID],RTRIM(HostelerID) as [Hosteler ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(Hostelname) as [Hostel name],RTRIM(HostelFeePayment.Class) as [Class],RTRIM(HostelFeePayment.Section) as [Section], RTRIM(HostelFeePayment.Session) as [Session],RTRIM(installment) as [installment], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(HostelFeePayment.ClassType) as [Class Type], RTRIM(HostelFeePayment.SchoolType) as [School Type] from Student,SchoolInfo,HostelFeePayment,Hosteler,Hostelinfo where SchoolInfo.S_ID=Student.SchoolID and Hosteler.H_ID=HostelFeePayment.HostelerID and HostelInfo.HI_ID=Hosteler.HostelID and Student.AdmissionNo=Hosteler.AdmissionNo and PaymentDate between @d1 and @d2 order by StudentName", con)
61 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
62 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value
63 adp = New SqlDataAdapter(cmd)
64 ds = New DataSet()
65 adp.Fill(ds, "Student")
66 dgw.DataSource = ds.Tables("Student").DefaultView
67 con.Close()
68 Catch ex As Exception
69 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
70 End Try
71 End Sub
72
73 Sub fillSession()
74 Try
75 con = New SqlConnection(cs)
76 con.Open()
77 adp = New SqlDataAdapter()
78 adp.SelectCommand = New SqlCommand("SELECT distinct (Session) FROM HostelFeePayment", con)
79 ds = New DataSet("ds")
80 adp.Fill(ds)
81 dtable = ds.Tables(0)
82 cmbSession.Items.Clear()
83 For Each drow As DataRow In dtable.Rows
84 cmbSession.Items.Add(drow(0).ToString())
85 Next
86 Catch ex As Exception
87 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
88 End Try
89 End Sub
90
91 Private Sub cmbSession_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSession.SelectedIndexChanged
92 Try
93 cmbClass.Enabled = True
94 con = New SqlConnection(cs)
95 con.Open()
96 Dim ct As String = "SELECT distinct RTRIM(Class) FROM HostelFeePayment where HostelFeePayment.Session=@d1"
97 cmd = New SqlCommand(ct)
98 cmd.Connection = con
99 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
100 rdr = cmd.ExecuteReader()
101 cmbClass.Items.Clear()
102 While rdr.Read
103 cmbClass.Items.Add(rdr(0))
104 End While
105 con.Close()
106 Catch ex As Exception
107 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
108 End Try
109
110 End Sub
111
112
113 Private Sub txtAdmissionNo_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtAdmissionNo.TextChanged
114 Try
115 con = New SqlConnection(cs)
116 con.Open()
117 cmd = New SqlCommand("Select RTRIM(HostelFeePayment.Id) as [ID], RTRIM(HFP_ID) as [HFP ID], RTRIM(PaymentID) as [Payment ID],RTRIM(HostelerID) as [Hosteler ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(Hostelname) as [Hostel name],RTRIM(HostelFeePayment.Class) as [Class],RTRIM(HostelFeePayment.Section) as [Section], RTRIM(HostelFeePayment.Session) as [Session],RTRIM(installment) as [installment], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(HostelFeePayment.ClassType) as [Class Type], RTRIM(HostelFeePayment.SchoolType) as [School Type] from Student,SchoolInfo,HostelFeePayment,Hosteler,Hostelinfo where SchoolInfo.S_ID=Student.SchoolID and Hosteler.H_ID=HostelFeePayment.HostelerID and HostelInfo.HI_ID=Hosteler.HostelID and Student.AdmissionNo=Hosteler.AdmissionNo and Student.AdmissionNo like '" & txtAdmissionNo.Text & "%' order by StudentName", con)
118 adp = New SqlDataAdapter(cmd)
119 ds = New DataSet()
120 adp.Fill(ds, "Student")
121 dgw.DataSource = ds.Tables("Student").DefaultView
122 con.Close()
123 Catch ex As Exception
124 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
125 End Try
126 End Sub
127 Sub Reset()
128 txtAdmissionNo.Text = ""
129 txtStudentName.Text = ""
130 cmbClass.SelectedIndex = -1
131 cmbSession.SelectedIndex = -1
132 cmbClass.Enabled = False
133 dtpDateFrom.Text = Today
134 dtpDateTo.Text = Now
135 GetData()
136 End Sub
137 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
138 Reset()
139 End Sub
140
141 Private Sub frmStudentRecord_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
142 fillSession()
143 GetData()
144 End Sub
145
146 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
147 Try
148 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
149 If lblSet.Text = "Hostel Fee Payment" Then
150 Me.Hide()
151 frmHostelFeePayment.Show()
152 frmHostelFeePayment.txtID.Text = dr.Cells(0).Value.ToString()
153 frmHostelFeePayment.txtHFPId.Text = dr.Cells(1).Value.ToString()
154 frmHostelFeePayment.txtFeePaymentID.Text = dr.Cells(2).Value.ToString()
155 frmHostelFeePayment.txtHostelerID.Text = dr.Cells(3).Value.ToString()
156 frmHostelFeePayment.txtAdmissionNo.Text = dr.Cells(4).Value.ToString()
157 frmHostelFeePayment.txtStudentName.Text = dr.Cells(5).Value.ToString()
158 frmHostelFeePayment.txtEnrollmentNo.Text = dr.Cells(6).Value.ToString()
159 frmHostelFeePayment.txtHostelName.Text = dr.Cells(7).Value.ToString() '
160 frmHostelFeePayment.txtSchoolName.Text = dr.Cells(8).Value.ToString()
161 frmHostelFeePayment.txtClass.Text = dr.Cells(9).Value.ToString()
162 frmHostelFeePayment.txtSection.Text = dr.Cells(10).Value.ToString()
163 frmHostelFeePayment.txtSession.Text = dr.Cells(11).Value.ToString()
164 frmHostelFeePayment.cmbInstallment.Text = dr.Cells(12).Value.ToString()
165 frmHostelFeePayment.txthostelFee.Text = dr.Cells(13).Value.ToString()
166 frmHostelFeePayment.txtDiscountPer.Text = dr.Cells(14).Value.ToString()
167 frmHostelFeePayment.txtDiscount.Text = dr.Cells(15).Value.ToString()
168 frmHostelFeePayment.txtPreviousDue.Text = dr.Cells(16).Value.ToString()
169 frmHostelFeePayment.txtFine.Text = dr.Cells(17).Value.ToString()
170 frmHostelFeePayment.txtGrandTotal.Text = dr.Cells(18).Value.ToString()
171 frmHostelFeePayment.txtTotalPaid.Text = dr.Cells(19).Value.ToString()
172 frmHostelFeePayment.cmbPaymentMode.Text = dr.Cells(20).Value.ToString()
173 frmHostelFeePayment.txtPaymentModeDetails.Text = dr.Cells(21).Value.ToString()
174 frmHostelFeePayment.dtpPaymentDate.Text = dr.Cells(22).Value.ToString()
175 frmHostelFeePayment.txtBalance.Text = dr.Cells(23).Value.ToString()
176 frmHostelFeePayment.txtClassType.Text = dr.Cells(24).Value.ToString()
177 frmHostelFeePayment.txtSchoolType.Text = dr.Cells(25).Value.ToString()
178 frmHostelFeePayment.btnDelete.Enabled = True
179 frmHostelFeePayment.btnPrint.Enabled = True
180 frmHostelFeePayment.btnUpdate.Enabled = True
181 frmHostelFeePayment.btnSave.Enabled = False
182 frmHostelFeePayment.Button2.Enabled = False
183 frmHostelFeePayment.dtpPaymentDate.Enabled = False
184 frmHostelFeePayment.cmbInstallment.Enabled = False
185 con = New SqlConnection(cs)
186 con.Open()
187 cmd = con.CreateCommand()
188 cmd.CommandText = "SELECT Installment FROM hostelfeepayment where ID=@d1"
189 cmd.Parameters.AddWithValue("@d1", dr.Cells(0).Value)
190 rdr = cmd.ExecuteReader()
191 If rdr.Read() Then
192 frmHostelFeePayment.cmbInstallment.Text = rdr.GetValue(0)
193 End If
194 If (rdr IsNot Nothing) Then
195 rdr.Close()
196 End If
197 If con.State = ConnectionState.Open Then
198 con.Close()
199 End If
200 lblSet.Text = ""
201 End If
202
203 Catch ex As Exception
204 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
205 End Try
206 End Sub
207
208 Private Sub dgw_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
209 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
210 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
211 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
212 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
213 End If
214 Dim b As Brush = SystemBrushes.ControlText
215 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
216
217 End Sub
218
219 Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
220 Dim rowsTotal, colsTotal As Short
221 Dim I, j, iC As Short
222 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
223 Dim xlApp As New Excel.Application
224 Try
225 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
226 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
227 xlApp.Visible = True
228
229 rowsTotal = dgw.RowCount
230 colsTotal = dgw.Columns.Count - 1
231 With excelWorksheet
232 .Cells.Select()
233 .Cells.Delete()
234 For iC = 0 To colsTotal
235 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
236 Next
237 For I = 0 To rowsTotal - 1
238 For j = 0 To colsTotal
239 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
240 Next j
241 Next I
242 .Rows("1:1").Font.FontStyle = "Bold"
243 .Rows("1:1").Font.Size = 12
244
245 .Cells.Columns.AutoFit()
246 .Cells.Select()
247 .Cells.EntireColumn.AutoFit()
248 .Cells(1, 1).Select()
249 End With
250 Catch ex As Exception
251 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
252 Finally
253 'RELEASE ALLOACTED RESOURCES
254 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
255 xlApp = Nothing
256 End Try
257 End Sub
258 End Class
